package com.cjbdi.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

import org.hibernate.engine.spi.SessionImplementor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Service;
import com.cjbdi.dao.ReportRepository;
import com.cjbdi.entity.Report;
import com.cjbdi.pagination.PageInfo;

@Service
@Transactional
public class ReportService {
	@Autowired
    private ReportRepository reportRepository;
	
    public void save(Report report) {
    	reportRepository.save(report);

    }
    
    public List<Report> findById(int id){
    	List<Report> list = reportRepository.findById(id);
    	return list;
    }
    
    public List<Report> findByFybm(String fybm){
    	List<Report> list = reportRepository.findByFybm(fybm);
    	return list;
    }
    
    public List<Report> findByTitle(String title){
    	List<Report> list = reportRepository.findByTitle(title);
    	return list;
    }
    public List<Report> findByParam(String fybm,String year,String type,String zsType,String timeDimension){
    	List<Report> list = reportRepository.findByParam(fybm, year, type, zsType, timeDimension);
    	return list;
    }
	public boolean delete(int id) {
		// TODO Auto-generated method stub
		Integer i = reportRepository.delete(id);
    	if(i>0) {
    		return true;
    	}else {
    		return false;
    	}
	}
	public PageInfo<Report> getPageByParam(String title,String fymc,String type,Pageable pageable){
		Page<Report> page = reportRepository.getPageByParam(title, fymc, type,pageable);
		int records = (int) page.getTotalElements();
        int pageSize = page.getSize();
        int total = records % pageSize == 0 ? records / pageSize : records / pageSize + 1;
        PageInfo<Report> pageInfo = new PageInfo<>();
        pageInfo.setPage(page.getNumber() + 1);//页码
        pageInfo.setPageSize(pageSize);//页面大小
        pageInfo.setRows(page.getContent());//分页结果
        pageInfo.setRecords(records);//总记录数
        pageInfo.setTotal(total);//总页数
		return pageInfo;
	}
	
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例
	public String getMapData(String fybm, String type, String date) {
		SessionImplementor session = entityManager.unwrap(SessionImplementor.class);
		Connection connection = session.connection();
		Statement statement;
		String result="";
		try {
			statement = connection.createStatement();
			String sql = "with c_fy as (\r\n" + 
					"select c_fbdm,(case when length(c_fbdm)-length(REPLACE(c_fbdm,'0','')) = 2 then 1\r\n" + 
					"  when length(c_fbdm)-length(REPLACE(c_fbdm,'0','')) = 1 then 2 \r\n" + 
					"when length(c_fbdm)-length(REPLACE(c_fbdm,'0','')) = 0 then 3 end)  as cfy_lb\r\n" + 
					"from fypz where c_fbdm = '"+fybm+"' ),\r\n" + 
					"\r\n" + 
					"--下级\r\n" + 
					"data_table as (select region,title,result from zxpt join fypz on fypz.c_fbdm = zxpt.fybm join c_fy  on substring(zxpt.fybm,1,cfy_lb) = substring(c_fy.c_fbdm,1,cfy_lb) where fy_lb = cfy_lb+1 and date = '"+date+"' and type = '"+type+"' ),\r\n" + 
					"\r\n" + 
					"maxmin_table as (select title,cast(round(cast(max(result) as NUMERIC),2) as VARCHAR) as max,cast(round(cast(min(result) as NUMERIC),2) as VARCHAR) as min from data_table GROUP BY title),\r\n" + 
					"\r\n" + 
					"zs_table as (select id_zs,title as zsmc,zs_lb,\r\n" + 
					"(select array_to_json(array_agg(row_to_json(a))) as map_data from (\r\n" + 
					"select region,cast(round(cast(result as NUMERIC),2) as VARCHAR) as result from data_table dt where  dt.title = zspz.title)a) ,(select array_to_json(array_agg(row_to_json(b))) as maxmin_data from (\r\n" + 
					"select max,min from maxmin_table mt where  mt.title = zspz.title)b) from zspz ),\r\n" + 
					"\r\n" + 
					"spzx_table as (select array_to_json(array_agg(row_to_json(c))) as spzx from( select zsmc,map_data,maxmin_data from zs_table where zs_lb = 'spzx' order by id_zs)c),\r\n" + 
					"\r\n" + 
					"shzl_table as (select array_to_json(array_agg(row_to_json(c))) as shzl from( select zsmc,map_data,maxmin_data from zs_table where zs_lb = 'shzl' order by id_zs )c)\r\n" + 
					"select row_to_json(d) as zs_data from (select spzx, shzl  from spzx_table, shzl_table )d \r\n" + 
					"\r\n" + 
					"";
			
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				result = rs.getString(1);

			}
			rs.close();
			statement.close();
//			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return result;
	}
	
}
